﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CNotebook.Inventory;
namespace CNotebook.Sales
{
    public class SalesReturnManager:ConnectionManager
    {
        #region Others
        public bool IsDocumentExists(string _DOCUMENT_NUM)
        {
            DataSet ds = new DataSet();

            SqlCommand oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM ORIN WHERE DOCNUM = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DOCNUM", _DOCUMENT_NUM));
            SqlDataAdapter _ADAPTER = new SqlDataAdapter();

            _ADAPTER.SelectCommand = oCommand;
            _ADAPTER.Fill(ds);

            int iCount = ds.Tables[0].Rows.Count;

            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public DataTable GetReturnTypes()
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM M_RETURNTYPE";

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);



            return ds.Tables[0];

        }

        public DataTable GetReturnReasons()
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM M_RETREASON";

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);



            return ds.Tables[0];

        }
        #endregion

        #region Add/Update Order

        public SqlException AddReturn(SalesReturnUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {


                oCommand.Connection = this.Connection;
                oCommand.CommandText = "INSERT INTO ORIN (DocNum, DocStatus, PostingDate, DueDate, DocDate, Location, StoreCode, CardCode, CardName, ContactPerson, Reference, RefSINo, Ref1, Ref2, Ref3, BillTo, ShipTo, SalesMan, Remarks, ReturnType, VATCode, VATRate, IsVATInclusive, GrossAmount, VATAmount, WithHoldingTax, Discount1, Discount2, Discount3, Discount4, TotalDiscount, NetAmount, DateCreated, CreatedBy, DateModified, ModifiedBy) VALUES " +
                                                            " (@DocNum, @DocStatus, @PostingDate, @DueDate, @DocDate, @Location, @StoreCode, @CardCode, @CardName, @ContactPerson, @Reference, @RefSINo, @Ref1, @Ref2, @Ref3, @BillTo, @ShipTo, @SalesMan, @Remarks, @ReturnType, @VATCode, @VATRate, @IsVATInclusive, @GrossAmount, @VATAmount, @WithHoldingTax,  @Discount1, @Discount2, @Discount3, @Discount4, @TotalDiscount, @NetAmount, GetDate(), @CreatedBy, GetDate(), @ModifiedBy)";

                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
                oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
                oCommand.Parameters.Add(new SqlParameter("@DueDate", oUnit.DueDate));
                oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.DocDate));
                oCommand.Parameters.Add(new SqlParameter("@Location", oUnit.Location));
                oCommand.Parameters.Add(new SqlParameter("@StoreCode", oUnit.StoreCode));
                oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
                oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
                oCommand.Parameters.Add(new SqlParameter("@ContactPerson", oUnit.ContactPerson));
                oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
                oCommand.Parameters.Add(new SqlParameter("@RefSINo", oUnit.ReferenceSINo));
                oCommand.Parameters.Add(new SqlParameter("@Ref1", oUnit.Ref1));
                oCommand.Parameters.Add(new SqlParameter("@Ref2", oUnit.Ref2));
                oCommand.Parameters.Add(new SqlParameter("@Ref3", oUnit.Ref3));
                oCommand.Parameters.Add(new SqlParameter("@BillTo", oUnit.BillTo));
                oCommand.Parameters.Add(new SqlParameter("@ShipTo", oUnit.ShipTo));
                oCommand.Parameters.Add(new SqlParameter("@SalesMan", oUnit.SalesMan));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
                oCommand.Parameters.Add(new SqlParameter("@ReturnType", oUnit.ReturnType));
                oCommand.Parameters.Add(new SqlParameter("@VATCode", oUnit.TaxCode));
                oCommand.Parameters.Add(new SqlParameter("@VATRate", oUnit.TaxRate));
                oCommand.Parameters.Add(new SqlParameter("@IsVATInclusive", oUnit.IsVATInclusive));

                oCommand.Parameters.Add(new SqlParameter("@GrossAmount", oUnit.GrossAmnt));

                oCommand.Parameters.Add(new SqlParameter("@VATAmount", oUnit.VATAmt));
                oCommand.Parameters.Add(new SqlParameter("@WithHoldingTax", oUnit.WithHoldingTax));
                oCommand.Parameters.Add(new SqlParameter("@Discount1", oUnit.Discount1));
                oCommand.Parameters.Add(new SqlParameter("@Discount2", oUnit.Discount2));
                oCommand.Parameters.Add(new SqlParameter("@Discount3", oUnit.Discount3));
                oCommand.Parameters.Add(new SqlParameter("@Discount4", oUnit.Discount4));
                oCommand.Parameters.Add(new SqlParameter("@TotalDiscount", oUnit.TotalDiscount));
                oCommand.Parameters.Add(new SqlParameter("@NetAmount", oUnit.NetAmount));


                oCommand.Parameters.Add(new SqlParameter("@CreatedBy", oUnit.CreatedBy));

                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));



                oCommand.ExecuteNonQuery();

                foreach (DataRow oRow in oUnit.LineItems.Rows)
                {
                    oCommand = new SqlCommand();
                    oCommand.Connection = this.Connection;

                    oCommand.CommandText = "INSERT INTO RIN1 (DocNum, ItemCode, ItemDesc, UoM, Qty, CQty, Reason, LotNumber, PriceListCode, UnitPrice, UnitCost, IsFree, DiscPrcnt, Discount, TotalLineDisc, Amount, ItemGuid) VALUES " +
                                                                    "(@DocNum, @ItemCode, @ItemDesc, @UoM, @Qty, @CQty, @Reason, @LotNumber, @PriceListCode, @UnitPrice, @UnitCost, @IsFree, @DiscPrcnt, @Discount, @TotalLineDisc, @Amount, @ItemGuid)";


                    oCommand.Parameters.Add(new SqlParameter("@PriceListCode", oRow["PriceListCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@ItemGuid", oRow["ItemGuid"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                    oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@ItemDesc", oRow["ItemDesc"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@Qty", Convert.ToDouble(oRow["Qty"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@CQty", Convert.ToDouble(oRow["CQty"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@Reason", oRow["Reason"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@LotNumber", oRow["LotNumber"].ToString()));

                    oCommand.Parameters.Add(new SqlParameter("@UnitPrice", Convert.ToDouble(oRow["UnitPrice"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@UnitCost", Convert.ToDouble(oRow["UnitCost"].ToString())));
                    if (oRow["IsFree"] != DBNull.Value)
                    {
                        oCommand.Parameters.Add(new SqlParameter("@IsFree", Convert.ToBoolean(oRow["IsFree"])));
                    }
                    else
                    {
                        oCommand.Parameters.Add(new SqlParameter("@IsFree", false));
                    }
                    oCommand.Parameters.Add(new SqlParameter("@DiscPrcnt", oRow["DiscPrcnt"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@Discount", Convert.ToDouble(oRow["Discount"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@TotalLineDisc", oRow["TotalLineDisc"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));

                    oCommand.ExecuteNonQuery();

                }


                //oCommand = new SqlCommand();
                //oCommand.Connection = this.Connection;
                //oCommand.CommandType = CommandType.StoredProcedure;
                //oCommand.CommandText = "SP_POST_SALESRETURN_ENTRIES";
                //oCommand.Parameters.Add(new SqlParameter("@DOCNUM", oUnit.DocumentNumber));

                //oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }

        }

        public SqlException UpdateReturn(SalesReturnUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE ORIN SET DocStatus=@DocStatus, PostingDate=@PostingDate, DueDate=@DueDate, DocDate=@DocDate, Location=@Location, CardCode=@CardCode, CardName=@CardName, ContactPerson=@ContactPerson, Reference=@Reference, RefSINo=@RefSINo, Ref1=@Ref1, Ref2=@Ref2, Ref3=@Ref3, BillTo=@BillTo, ShipTo=@ShipTo, SalesMan=@SalesMan, Remarks=@Remarks, ReturnType=@ReturnType, GrossAmount=@GrossAmount, VATAmount=@VATAmount, WithHoldingTax=@WithHoldingTax, Discount1=@Discount1, Discount2=@Discount2, Discount3=@Discount3, Discount4=@Discount4, TotalDiscount=@TotalDiscount, NetAmount=@NetAmount, DateModified=GetDate(), ModifiedBy=@ModifiedBy" +
                                                            " WHERE DocNum=@DocNum";

                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
                oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
                oCommand.Parameters.Add(new SqlParameter("@DueDate", oUnit.DueDate));
                oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.DocDate));
                oCommand.Parameters.Add(new SqlParameter("@Location", oUnit.Location));
                oCommand.Parameters.Add(new SqlParameter("@StoreCode", oUnit.StoreCode));
                oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
                oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
                oCommand.Parameters.Add(new SqlParameter("@ContactPerson", oUnit.ContactPerson));
                oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
                oCommand.Parameters.Add(new SqlParameter("@RefSINo", oUnit.ReferenceSINo));
                oCommand.Parameters.Add(new SqlParameter("@Ref1", oUnit.Ref1));
                oCommand.Parameters.Add(new SqlParameter("@Ref2", oUnit.Ref2));
                oCommand.Parameters.Add(new SqlParameter("@Ref3", oUnit.Ref3));
                oCommand.Parameters.Add(new SqlParameter("@BillTo", oUnit.BillTo));
                oCommand.Parameters.Add(new SqlParameter("@ShipTo", oUnit.ShipTo));
                oCommand.Parameters.Add(new SqlParameter("@SalesMan", oUnit.SalesMan));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
                oCommand.Parameters.Add(new SqlParameter("@ReturnType", oUnit.ReturnType));
                oCommand.Parameters.Add(new SqlParameter("@GrossAmount", oUnit.GrossAmnt));
                oCommand.Parameters.Add(new SqlParameter("@VATAmount", oUnit.VATAmt));
                oCommand.Parameters.Add(new SqlParameter("@WithHoldingTax", oUnit.WithHoldingTax));
                oCommand.Parameters.Add(new SqlParameter("@Discount1", oUnit.Discount1));
                oCommand.Parameters.Add(new SqlParameter("@Discount2", oUnit.Discount2));
                oCommand.Parameters.Add(new SqlParameter("@Discount3", oUnit.Discount3));
                oCommand.Parameters.Add(new SqlParameter("@Discount4", oUnit.Discount4));
                oCommand.Parameters.Add(new SqlParameter("@TotalDiscount", oUnit.TotalDiscount));
                oCommand.Parameters.Add(new SqlParameter("@NetAmount", oUnit.NetAmount));

                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));

                oCommand.ExecuteNonQuery();

                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "DELETE RIN1 WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.ExecuteNonQuery();

                foreach (DataRow oRow in oUnit.LineItems.Rows)
                {

                    if (oRow.RowState != DataRowState.Deleted)
                    {
                        oCommand = new SqlCommand();
                        oCommand.Connection = this.Connection;

                        oCommand.CommandText = "INSERT INTO RIN1 (DocNum, ItemCode, ItemDesc, UoM, Qty, CQty, Reason, LotNumber, PriceListCode, UnitPrice, UnitCost, IsFree, DiscPrcnt, Discount, TotalLineDisc, Amount, ItemGuid) VALUES " +
                                                                    "(@DocNum, @ItemCode, @ItemDesc, @UoM, @Qty, @CQty, @Reason, @LotNumber, @PriceListCode, @UnitPrice, @UnitCost, @IsFree, @DiscPrcnt, @Discount, @TotalLineDisc, @Amount, @ItemGuid)";

                        oCommand.Parameters.Add(new SqlParameter("@PriceListCode", oRow["PriceListCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@ItemGuid", oRow["ItemGuid"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                        oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@ItemDesc", oRow["ItemDesc"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                        if (oRow["Qty"] == DBNull.Value)
                        {
                            int _ZeroValue = 0;
                            oRow["Qty"] = _ZeroValue;
                        }
                        oCommand.Parameters.Add(new SqlParameter("@Qty", Convert.ToDouble(oRow["Qty"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@CQty", Convert.ToDouble(oRow["CQty"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@Reason", oRow["Reason"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@LotNumber", oRow["LotNumber"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@UnitPrice", Convert.ToDouble(oRow["UnitPrice"].ToString())));
                        if (oRow["UnitCost"] != DBNull.Value)
                        {
                            if (oRow["UnitCost"].ToString().Trim() != "")
                            {
                                oCommand.Parameters.Add(new SqlParameter("@UnitCost", Convert.ToDouble(oRow["UnitCost"].ToString())));
                            }
                            else
                            {
                                oCommand.Parameters.Add(new SqlParameter("@UnitCost", 0));
                            }
                        }
                        else
                        {
                            oCommand.Parameters.Add(new SqlParameter("@UnitCost", 0));
                        }
                        if (oRow["IsFree"] != DBNull.Value)
                        {
                            oCommand.Parameters.Add(new SqlParameter("@IsFree", Convert.ToBoolean(oRow["IsFree"])));
                        }
                        else
                        {
                            oCommand.Parameters.Add(new SqlParameter("@IsFree", false));
                        }
                        oCommand.Parameters.Add(new SqlParameter("@DiscPrcnt", oRow["DiscPrcnt"].ToString()));
                        if (oRow["Discount"] != DBNull.Value)
                        {
                            if (oRow["Discount"].ToString() != "")
                            {
                                oCommand.Parameters.Add(new SqlParameter("@Discount", Convert.ToDouble(oRow["Discount"].ToString())));
                            }
                            else
                            {
                                oCommand.Parameters.Add(new SqlParameter("@Discount", 0));
                            }
                        }
                        else
                        {
                            oCommand.Parameters.Add(new SqlParameter("@Discount", 0));
                        }
                        oCommand.Parameters.Add(new SqlParameter("@TotalLineDisc", oRow["TotalLineDisc"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));

                        oCommand.ExecuteNonQuery();

                    }

                }

                #region Removing GL Accounts
                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandType = CommandType.Text;
                oCommand.CommandText = "DELETE FROM RIN2 WHERE DOCNUM=@DOCNUM";
                oCommand.Parameters.Add(new SqlParameter("@DOCNUM", oUnit.DocumentNumber));

                oCommand.ExecuteNonQuery();
                #endregion

                #region inserting GL Account Entries
                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandType = CommandType.StoredProcedure;
                oCommand.CommandText = "SP_POST_SALESRETURN_ENTRIES";
                oCommand.Parameters.Add(new SqlParameter("@DOCNUM", oUnit.DocumentNumber));

                oCommand.ExecuteNonQuery();
                #endregion

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException AddDistributionEntries(string DocumentNumber, DataTable dtAccountingEntries)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                #region Accounting Entries


                oCommand = new SqlCommand();

                oCommand.Connection = this.Connection;
                oCommand.CommandType = CommandType.Text;

                oCommand.CommandText = "DELETE [RIN2] WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", DocumentNumber));
                oCommand.ExecuteNonQuery();

                foreach (DataRow oRow in dtAccountingEntries.Rows)
                {
                    if (oRow.RowState != DataRowState.Deleted)
                    {
                        oCommand = new SqlCommand();

                        oCommand.Connection = this.Connection;
                        oCommand.CommandType = CommandType.Text;

                        oCommand.CommandText = "INSERT INTO [RIN2] (DocNum, AcctCode, AcctName, Particulars, DebitAmt, CreditAmt) VALUES (@DocNum, @AcctCode, @AcctName, @Particulars, @DebitAmt, @CreditAmt)";
                        oCommand.Parameters.Add(new SqlParameter("@DocNum", DocumentNumber));
                        oCommand.Parameters.Add(new SqlParameter("@AcctCode", oRow["AcctCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@AcctName", oRow["AcctName"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Particulars", oRow["Particulars"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@DebitAmt", oRow["DebitAmt"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@CreditAmt", oRow["CreditAmt"].ToString()));

                        oCommand.ExecuteNonQuery();
                    }
                }
                #endregion

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
        }
        
        public void SetAppliedAmount(string DocumentNo, string _AMOUNT)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE ORIN SET AppliedAmount=AppliedAmount+@AppliedAmount WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", DocumentNo));
            oCommand.Parameters.Add(new SqlParameter("@AppliedAmount", _AMOUNT));

            oCommand.ExecuteNonQuery();
        }
        #endregion

        #region Post/Cancel/Close Order
        public void PostOrder(string _WAREHOUSE, string _INVOICE_NO, string _DOCNUMBER, string _ACTIVEUSERID)
        {
            SqlCommand oCommand = new SqlCommand();
            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.Text;
            oCommand.CommandText = "UPDATE ORIN SET DOCSTATUS='Posted' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCNUMBER));
            oCommand.ExecuteNonQuery();


            #region Updating Stocks
            string selectSQL = "SELECT * FROM RIN1 WHERE DocNum=@DocNum";


            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = selectSQL;
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCNUMBER));
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);


            foreach (DataRow oRow in ds.Tables[0].Rows)
            {
                StockManager oManager = new StockManager();
                ItemManager _ITEM_MASTER = new ItemManager();

                oManager.Connection = this.Connection;
                _ITEM_MASTER.Connection = this.Connection;

                if (_ITEM_MASTER.IsInventoryItem(oRow["ItemCode"].ToString()) == true)
                {
                    if (oManager.IsLotNumberExists(_WAREHOUSE, oRow["ItemCode"].ToString(), oRow["LotNumber"].ToString()))
                    {
                        oManager.UpdateStocks(_WAREHOUSE, oRow["ItemCode"].ToString(), oRow["LotNumber"].ToString(), oRow["Qty"].ToString(), oRow["UnitCost"].ToString());
                    }
                    else
                    {
                        WarehouseManager oWhs = new WarehouseManager();
                        oWhs.ConnectionString = this.ConnectionString;
                        oWhs.Open();
                        DataTable dt = oWhs.GetWarehouse(_WAREHOUSE);
                        oWhs.Close();
                        oManager.AddStocks(_WAREHOUSE, dt.Rows[0]["Dscription"].ToString(), oRow["ItemCode"].ToString(), oRow["ItemDesc"].ToString(), oRow["Qty"].ToString(), oRow["UoM"].ToString(), oRow["LotNumber"].ToString(), System.DateTime.Now.ToString(), System.DateTime.Now.ToString(), oRow["UnitCost"].ToString(), "N/A", "Sales Return - " + oRow["DocNum"].ToString());
                    }
                }
                else
                {
                    DataTable _FREE_TABLE = new DataTable();
                    KitManager oBOMManager = new KitManager();

                    oBOMManager.Connection = this.Connection;

                    _FREE_TABLE = oBOMManager.GetLineItems(oRow["ItemCode"].ToString());    // GETTING THE STRUCTURE

                    int _QtyMultiplier = Convert.ToInt32(oRow["Qty"]);           // GETTING THE MULTIPLIER

                    #region Looping with the Product Structure
                    foreach (DataRow BoMRow in _FREE_TABLE.Rows)
                    {
                        if (oManager.IsLotNumberExists(_WAREHOUSE, BoMRow["ItemCode"].ToString(), oRow["LotNumber"].ToString()))
                        {
                            oManager.UpdateStocks(_WAREHOUSE, BoMRow["ItemCode"].ToString(), oRow["LotNumber"].ToString(), (Convert.ToDouble(BoMRow["Qty"]) * Convert.ToDouble(_QtyMultiplier)).ToString());
                        }
                        else
                        {
                            WarehouseManager oWhs = new WarehouseManager();
                            oWhs.ConnectionString = this.ConnectionString;
                            oWhs.Open();
                            DataTable dt = oWhs.GetWarehouse(_WAREHOUSE);
                            oWhs.Close();
                            oManager.AddStocks(_WAREHOUSE, dt.Rows[0]["Dscription"].ToString(), BoMRow["ItemCode"].ToString(), BoMRow["ItemDesc"].ToString(), (Convert.ToDouble(BoMRow["Qty"]) * Convert.ToDouble(_QtyMultiplier)).ToString(), BoMRow["UoM"].ToString(), oRow["LotNumber"].ToString(), System.DateTime.Now.ToString(), System.DateTime.Now.ToString());
                        }
                    }
                    #endregion

                }
            }
            #endregion

            #region Update Status
            if (_INVOICE_NO.Trim() != "")
            {
                #region Getting the Line Items of SalesOrder
                DeliveryManager oInvoiceManager = new DeliveryManager();
                oInvoiceManager.Connection = this.Connection;
                DataTable dtInvoice = new DataTable();


                dtInvoice = oInvoiceManager.GetDeliveryLineItems(_INVOICE_NO);

                #endregion

                int iStatus = 2; // 1 = Open, 2 = Closed

                foreach (DataRow oRow in dtInvoice.Rows)
                {
                    oCommand = new SqlCommand();

                    oCommand.Connection = this.Connection;
                    oCommand.CommandText = "SELECT SUM(Qty) Qty FROM ORIN JOIN RIN1 ON ORIN.DOCNUM = RIN1.DOCNUM WHERE RIN1.ItemGuid = @ItemGuid AND (ORIN.DOCSTATUS <> 'Draft' or ORIN.DocStatus <> 'Canceled')";

                    oCommand.Parameters.Add(new SqlParameter("@ItemGuid", oRow["ItemGuid"].ToString()));

                    DataTable dtRIN1 = new DataTable();
                    SqlDataAdapter dAdapter = new SqlDataAdapter();
                    dAdapter.SelectCommand = oCommand;
                    dAdapter.Fill(dtRIN1);

                    DataRow drRIN1_Row = dtRIN1.Rows[0];

                    if (Convert.ToDouble(oRow["Qty"]) != Convert.ToDouble(drRIN1_Row["Qty"]))
                    {
                        iStatus = 1;
                        break;
                    }




                }

                if (iStatus == 2)
                {
                    oInvoiceManager.CloseDR(_INVOICE_NO, _ACTIVEUSERID);
                }


                //if (iStatus == 1)
                //{
                //    oInvoiceManager.SetOrderOpen(_INVOICE_NO);
                //}
            }
            #endregion
        }

        public void CancelOrder(string sOrderNo)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE ORIN SET DOCSTATUS='Canceled' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sOrderNo));
            oCommand.ExecuteNonQuery();
        }

        public void CloseOrder(string sOrderNo)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE ORIN SET DOCSTATUS='Closed' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sOrderNo));
            oCommand.ExecuteNonQuery();
        }
        #endregion

        #region Getting Record
        public DataTable GetAccountingDistribution(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM RIN2 WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }
        public DataRow GetReturn(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM ORIN WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            DataRow oRow = ds.Tables[0].Rows[0];

            return oRow;
        }

        public DataTable GetReturnLineItems(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM RIN1 WHERE DocNum = @DOCNUM ORDER BY RECINDEX ASC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);



            return ds.Tables[0];
        }

        #endregion

        #region Browsing Record

        public DataTable BrowseReturnsByDocumentNumber(string OutletCode, string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            sDocumentNumber = "%" + sDocumentNumber.Trim() + "%";
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT *, NetAmount - AppliedAmount TotalBalance FROM ORIN WHERE DocNum LIKE @DOCNUM and CardCode=@CardCode ORDER BY RECNO DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));
            oCommand.Parameters.Add(new SqlParameter("@CardCode", OutletCode));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            //DataRow oRow = ds.Tables[0].Rows[0];

            return ds.Tables[0];
        }

        public DataTable BrowseReturnsByDocumentNumber(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            sDocumentNumber = "%" + sDocumentNumber.Trim() + "%";
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT *, NetAmount - AppliedAmount TotalBalance FROM ORIN WHERE DocNum LIKE @DOCNUM ORDER BY RECNO DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            //DataRow oRow = ds.Tables[0].Rows[0];

            return ds.Tables[0];
        }

        public DataTable BrowseReturnsByIRS(string _IRS_NUMBER)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            _IRS_NUMBER = "%" + _IRS_NUMBER.Trim() + "%";
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT *, NetAmount - AppliedAmount TotalBalance FROM ORIN WHERE Ref1 LIKE @Ref1 ORDER BY RECNO DESC";
            oCommand.Parameters.Add(new SqlParameter("@Ref1", _IRS_NUMBER));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            //DataRow oRow = ds.Tables[0].Rows[0];

            return ds.Tables[0];
        }

        public DataTable BrowseReturnsByIRS(string OuletCode, string _IRS_NUMBER)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            _IRS_NUMBER = "%" + _IRS_NUMBER.Trim() + "%";
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT *, NetAmount - AppliedAmount TotalBalance FROM ORIN WHERE CardCode=@CardCode AND Ref1 LIKE @Ref1 ORDER BY RECNO DESC";
            oCommand.Parameters.Add(new SqlParameter("@Ref1", _IRS_NUMBER));
            oCommand.Parameters.Add(new SqlParameter("@CardCode", OuletCode));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            //DataRow oRow = ds.Tables[0].Rows[0];

            return ds.Tables[0];
        }

        public DataTable BrowseReturnsByRTV(string OuletCode, string _RTV_NUMBER)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            _RTV_NUMBER = "%" + _RTV_NUMBER.Trim() + "%";
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT *, NetAmount - AppliedAmount TotalBalance FROM ORIN WHERE CardCode=@CardCode AND Ref3 LIKE @Ref3 ORDER BY RECNO DESC";
            oCommand.Parameters.Add(new SqlParameter("@Ref3", _RTV_NUMBER));
            oCommand.Parameters.Add(new SqlParameter("@CardCode", OuletCode));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            //DataRow oRow = ds.Tables[0].Rows[0];

            return ds.Tables[0];
        }

        public DataTable BrowseReturnsByRTV(string _RTV_NUMBER)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            _RTV_NUMBER = "%" + _RTV_NUMBER.Trim() + "%";
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT *, NetAmount - AppliedAmount TotalBalance FROM ORIN WHERE Ref3 LIKE @Ref3 ORDER BY RECNO DESC";
            oCommand.Parameters.Add(new SqlParameter("@Ref3", _RTV_NUMBER));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            //DataRow oRow = ds.Tables[0].Rows[0];

            return ds.Tables[0];
        }

        public DataTable BrowseReturnsByCustomer(string _CARDNAME)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            _CARDNAME = "%" + _CARDNAME.Trim() + "%";
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT *, NetAmount - AppliedAmount TotalBalance FROM ORIN WHERE CardName LIKE @CardName ORDER BY RECNO DESC";
            oCommand.Parameters.Add(new SqlParameter("@CardName", _CARDNAME));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            //DataRow oRow = ds.Tables[0].Rows[0];

            return ds.Tables[0];
        }
        
        public DataTable BrowseReturnsByCustomerCode(string _CARDCODE)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            _CARDCODE = "%" + _CARDCODE.Trim() + "%";
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT *, NetAmount - AppliedAmount TotalBalance FROM ORIN WHERE CardCode LIKE @CardCode ORDER BY RECNO DESC";
            oCommand.Parameters.Add(new SqlParameter("@CardCode", _CARDCODE));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            //DataRow oRow = ds.Tables[0].Rows[0];

            return ds.Tables[0];
        }


        #endregion

        #region Report Retreivable
        //--------------------------------------------------------------------------
        // HEADERS

        public DataTable GetSalesReturnLogByDocument(DateTime _FROM, DateTime _TO, bool _Draft, bool _Posted, bool _Open, bool _Closed)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_LOGS_SR";
            oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
            oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

            oCommand.Parameters.Add(new SqlParameter("@WITHDRAFT", Convert.ToInt16(_Draft)));
            oCommand.Parameters.Add(new SqlParameter("@WITHPOSTED", Convert.ToInt16(_Posted)));
            oCommand.Parameters.Add(new SqlParameter("@WITHOPEN", Convert.ToInt16(_Open)));
            oCommand.Parameters.Add(new SqlParameter("@WITHCLOSED", Convert.ToInt16(_Closed)));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetSalesReturnLogByItem(DateTime _FROM, DateTime _TO, bool _Draft, bool _Posted, bool _Open, bool _Closed)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_LOGS_SR_PER_ITEM";
            oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
            oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

            oCommand.Parameters.Add(new SqlParameter("@WITHDRAFT", Convert.ToInt16(_Draft)));
            oCommand.Parameters.Add(new SqlParameter("@WITHPOSTED", Convert.ToInt16(_Posted)));
            oCommand.Parameters.Add(new SqlParameter("@WITHOPEN", Convert.ToInt16(_Open)));
            oCommand.Parameters.Add(new SqlParameter("@WITHCLOSED", Convert.ToInt16(_Closed)));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetSalesReturnStatus(DateTime _FROM, DateTime _TO)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_SALESRETURN_STATUS";
            oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
            oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        #endregion
    }
}
